package com.fufan.mtsaas.tools;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;

/**
 * Excel生成工具
 *
 * @version 1.0
 * @auther Liby
 * @create 2018/5/16
 **/
@Slf4j
public class ExcelCreateUtil {

    public static void exportExcel(HttpServletResponse response,String fileName,ExcelData data){
        response.setHeader("Content-Type","application/vnd.ms-excel");
        try {
            response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
            exportExcel(data,response.getOutputStream());
        } catch (UnsupportedEncodingException e) {
            log.error("Excel导出-设置请求头出错：",e);
        } catch (IOException e) {
            log.error("Excel导出-数据流出错：",e);
        }
    }

    public static void exportExcel(ExcelData data, OutputStream out) {
        XSSFWorkbook wb = new XSSFWorkbook();
        String sheetName = data.getName();
        if(null == sheetName){
            sheetName = "Sheet1";
        }
        XSSFSheet sheet = wb.createSheet(sheetName);
        writeExcel(wb,sheet,data);
        try {
            wb.write(out);
            out.flush();
        } catch (IOException e) {
            log.error("Excel导出-数据流出错：",e);
        }finally {
            try {
                out.close();
            } catch (IOException e) {
                log.error("Excel导出-数据流关闭出错：",e);
            }
        }
    }

    private static void writeExcel(XSSFWorkbook wb,XSSFSheet sheet,ExcelData data){
        int rowIndex = 0;
        rowIndex = writeTitleToExcel(wb,sheet,data.getTitles());
        writeRowsToExcel(wb,sheet,data.getRows(),rowIndex);
        autoSizeColumns(sheet,data.getTitles().size()+1);
    }

    private static int writeTitleToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
        int rowIndex =0;
        /**
         * 表标题样式
         */
        XSSFFont titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        Row titleRow = sheet.createRow(rowIndex);
        titleRow.setHeightInPoints(25);
        int colIndex = 0;
        for(String field : titles){
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }
    private static int writeRowsToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;
        /**
         * 设置数据样式
         */
        XSSFFont dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        for(List<Object> rowData:rows){
            Row dataRow = sheet.createRow(rowIndex);
            dataRow.setHeightInPoints(25);
            colIndex = 0;
            for(Object cellData:rowData){
                Cell cell = dataRow.createCell(colIndex);
                if(cellData !=null){
                    cell.setCellValue(cellData.toString());
                }else{
                    cell.setCellValue("");
                }
                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    private static void autoSizeColumns(XSSFSheet sheet, int columnNumber) {
        for(int i = 0;i<columnNumber;i++){
            int orgWidth= sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i,true);
            int newWidth =  sheet.getColumnWidth(i)+100;
            if(newWidth>orgWidth){
                if(newWidth < 65000){
                    sheet.setColumnWidth(i, newWidth);
                }else{
                    sheet.setColumnWidth(i, orgWidth);
                    sheet.autoSizeColumn(i,false);
                    log.error("Excel导出-特别设置： i={}， orgWidth={}, newWidth={}", i, orgWidth, newWidth);
                }
            }else{
                sheet.setColumnWidth(i,orgWidth);
            }
        }
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderBottom(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT,color);
    }
}
